package com.cencat.driver.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.cencat.driver.entity.DriverMessage;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 司机消息数据访问层接口
 * 
 * @author cencat
 * @since 2024-01-01
 */
@Repository
@Mapper
public interface DriverMessageMapper extends BaseMapper<DriverMessage> {

    /**
     * 根据司机ID查询消息列表
     * 
     * @param driverId 司机ID
     * @param tenantId 租户ID
     * @return 消息列表
     */
    @Select("SELECT * FROM driver_message WHERE driver_id = #{driverId} AND tenant_id = #{tenantId} AND deleted = 0 ORDER BY create_time DESC")
    List<DriverMessage> selectByDriverId(@Param("driverId") Long driverId, @Param("tenantId") Long tenantId);

    /**
     * 根据消息类型查询消息列表
     * 
     * @param messageType 消息类型
     * @param tenantId 租户ID
     * @return 消息列表
     */
    @Select("SELECT * FROM driver_message WHERE message_type = #{messageType} AND tenant_id = #{tenantId} AND deleted = 0 ORDER BY create_time DESC")
    List<DriverMessage> selectByMessageType(@Param("messageType") Integer messageType, @Param("tenantId") Long tenantId);

    /**
     * 根据优先级查询消息列表
     * 
     * @param priority 优先级
     * @param tenantId 租户ID
     * @return 消息列表
     */
    @Select("SELECT * FROM driver_message WHERE priority = #{priority} AND tenant_id = #{tenantId} AND deleted = 0 ORDER BY create_time DESC")
    List<DriverMessage> selectByPriority(@Param("priority") Integer priority, @Param("tenantId") Long tenantId);

    /**
     * 查询司机的未读消息列表
     * 
     * @param driverId 司机ID
     * @param tenantId 租户ID
     * @return 未读消息列表
     */
    @Select("SELECT * FROM driver_message WHERE driver_id = #{driverId} AND is_read = 0 AND tenant_id = #{tenantId} AND deleted = 0 ORDER BY priority DESC, create_time DESC")
    List<DriverMessage> selectUnreadByDriverId(@Param("driverId") Long driverId, @Param("tenantId") Long tenantId);

    /**
     * 查询司机的已读消息列表
     * 
     * @param driverId 司机ID
     * @param tenantId 租户ID
     * @return 已读消息列表
     */
    @Select("SELECT * FROM driver_message WHERE driver_id = #{driverId} AND is_read = 1 AND tenant_id = #{tenantId} AND deleted = 0 ORDER BY read_time DESC")
    List<DriverMessage> selectReadByDriverId(@Param("driverId") Long driverId, @Param("tenantId") Long tenantId);

    /**
     * 查询司机的紧急消息列表
     * 
     * @param driverId 司机ID
     * @param tenantId 租户ID
     * @return 紧急消息列表
     */
    @Select("SELECT * FROM driver_message WHERE driver_id = #{driverId} AND priority = 4 AND tenant_id = #{tenantId} AND deleted = 0 ORDER BY create_time DESC")
    List<DriverMessage> selectUrgentByDriverId(@Param("driverId") Long driverId, @Param("tenantId") Long tenantId);

    /**
     * 分页查询司机消息列表
     * 
     * @param page 分页参数
     * @param driverId 司机ID
     * @param messageType 消息类型
     * @param isRead 是否已读
     * @param priority 优先级
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param tenantId 租户ID
     * @return 分页结果
     */
    @Select("<script>" +
            "SELECT * FROM driver_message WHERE tenant_id = #{tenantId} AND deleted = 0" +
            "<if test='driverId != null'>" +
            " AND driver_id = #{driverId}" +
            "</if>" +
            "<if test='messageType != null'>" +
            " AND message_type = #{messageType}" +
            "</if>" +
            "<if test='isRead != null'>" +
            " AND is_read = #{isRead}" +
            "</if>" +
            "<if test='priority != null'>" +
            " AND priority = #{priority}" +
            "</if>" +
            "<if test='startTime != null'>" +
            " AND create_time >= #{startTime}" +
            "</if>" +
            "<if test='endTime != null'>" +
            " AND create_time <= #{endTime}" +
            "</if>" +
            " ORDER BY priority DESC, create_time DESC" +
            "</script>")
    IPage<DriverMessage> selectPageWithConditions(Page<DriverMessage> page,
                                                 @Param("driverId") Long driverId,
                                                 @Param("messageType") Integer messageType,
                                                 @Param("isRead") Integer isRead,
                                                 @Param("priority") Integer priority,
                                                 @Param("startTime") LocalDateTime startTime,
                                                 @Param("endTime") LocalDateTime endTime,
                                                 @Param("tenantId") Long tenantId);

    /**
     * 统计司机消息数量
     * 
     * @param driverId 司机ID
     * @param tenantId 租户ID
     * @return 消息总数
     */
    @Select("SELECT COUNT(*) FROM driver_message WHERE driver_id = #{driverId} AND tenant_id = #{tenantId} AND deleted = 0")
    Long countByDriverId(@Param("driverId") Long driverId, @Param("tenantId") Long tenantId);

    /**
     * 统计司机未读消息数量
     * 
     * @param driverId 司机ID
     * @param tenantId 租户ID
     * @return 未读消息数量
     */
    @Select("SELECT COUNT(*) FROM driver_message WHERE driver_id = #{driverId} AND is_read = 0 AND tenant_id = #{tenantId} AND deleted = 0")
    Long countUnreadByDriverId(@Param("driverId") Long driverId, @Param("tenantId") Long tenantId);

    /**
     * 统计司机指定类型的消息数量
     * 
     * @param driverId 司机ID
     * @param messageType 消息类型
     * @param tenantId 租户ID
     * @return 消息数量
     */
    @Select("SELECT COUNT(*) FROM driver_message WHERE driver_id = #{driverId} AND message_type = #{messageType} AND tenant_id = #{tenantId} AND deleted = 0")
    Long countByDriverIdAndType(@Param("driverId") Long driverId, @Param("messageType") Integer messageType, @Param("tenantId") Long tenantId);

    /**
     * 统计司机紧急消息数量
     * 
     * @param driverId 司机ID
     * @param tenantId 租户ID
     * @return 紧急消息数量
     */
    @Select("SELECT COUNT(*) FROM driver_message WHERE driver_id = #{driverId} AND priority = 4 AND tenant_id = #{tenantId} AND deleted = 0")
    Long countUrgentByDriverId(@Param("driverId") Long driverId, @Param("tenantId") Long tenantId);

    /**
     * 标记消息为已读
     * 
     * @param id 消息ID
     * @param driverId 司机ID
     * @param tenantId 租户ID
     * @return 更新行数
     */
    @Update("UPDATE driver_message SET is_read = 1, read_time = NOW(), update_time = NOW() WHERE id = #{id} AND driver_id = #{driverId} AND tenant_id = #{tenantId}")
    int markAsRead(@Param("id") Long id, @Param("driverId") Long driverId, @Param("tenantId") Long tenantId);

    /**
     * 标记消息为未读
     * 
     * @param id 消息ID
     * @param driverId 司机ID
     * @param tenantId 租户ID
     * @return 更新行数
     */
    @Update("UPDATE driver_message SET is_read = 0, read_time = NULL, update_time = NOW() WHERE id = #{id} AND driver_id = #{driverId} AND tenant_id = #{tenantId}")
    int markAsUnread(@Param("id") Long id, @Param("driverId") Long driverId, @Param("tenantId") Long tenantId);

    /**
     * 批量标记消息为已读
     * 
     * @param ids 消息ID列表
     * @param driverId 司机ID
     * @param tenantId 租户ID
     * @return 更新行数
     */
    @Update("<script>" +
            "UPDATE driver_message SET is_read = 1, read_time = NOW(), update_time = NOW() " +
            "WHERE driver_id = #{driverId} AND tenant_id = #{tenantId} AND id IN" +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    int batchMarkAsRead(@Param("ids") List<Long> ids, @Param("driverId") Long driverId, @Param("tenantId") Long tenantId);

    /**
     * 标记司机所有消息为已读
     * 
     * @param driverId 司机ID
     * @param tenantId 租户ID
     * @return 更新行数
     */
    @Update("UPDATE driver_message SET is_read = 1, read_time = NOW(), update_time = NOW() WHERE driver_id = #{driverId} AND is_read = 0 AND tenant_id = #{tenantId}")
    int markAllAsReadByDriverId(@Param("driverId") Long driverId, @Param("tenantId") Long tenantId);

    /**
     * 删除过期消息
     * 
     * @param tenantId 租户ID
     * @return 删除行数
     */
    @Update("UPDATE driver_message SET deleted = 1, update_time = NOW() WHERE expire_time IS NOT NULL AND expire_time < NOW() AND tenant_id = #{tenantId}")
    int deleteExpiredMessages(@Param("tenantId") Long tenantId);

    /**
     * 批量删除消息
     * 
     * @param ids 消息ID列表
     * @param driverId 司机ID
     * @param tenantId 租户ID
     * @return 删除行数
     */
    @Update("<script>" +
            "UPDATE driver_message SET deleted = 1, update_time = NOW() " +
            "WHERE driver_id = #{driverId} AND tenant_id = #{tenantId} AND id IN" +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    int batchDeleteByIds(@Param("ids") List<Long> ids, @Param("driverId") Long driverId, @Param("tenantId") Long tenantId);

    /**
     * 清空司机所有消息
     * 
     * @param driverId 司机ID
     * @param tenantId 租户ID
     * @return 删除行数
     */
    @Update("UPDATE driver_message SET deleted = 1, update_time = NOW() WHERE driver_id = #{driverId} AND tenant_id = #{tenantId}")
    int clearAllByDriverId(@Param("driverId") Long driverId, @Param("tenantId") Long tenantId);

    /**
     * 查询即将过期的消息（24小时内）
     * 
     * @param tenantId 租户ID
     * @return 即将过期的消息列表
     */
    @Select("SELECT * FROM driver_message WHERE expire_time IS NOT NULL AND " +
            "expire_time BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 24 HOUR) " +
            "AND tenant_id = #{tenantId} AND deleted = 0 ORDER BY expire_time ASC")
    List<DriverMessage> selectExpiringSoon(@Param("tenantId") Long tenantId);

    /**
     * 根据关联ID查询消息列表
     * 
     * @param relatedId 关联ID
     * @param tenantId 租户ID
     * @return 消息列表
     */
    @Select("SELECT * FROM driver_message WHERE related_id = #{relatedId} AND tenant_id = #{tenantId} AND deleted = 0 ORDER BY create_time DESC")
    List<DriverMessage> selectByRelatedId(@Param("relatedId") Long relatedId, @Param("tenantId") Long tenantId);

    /**
     * 查询司机今日消息统计
     * 
     * @param driverId 司机ID
     * @param tenantId 租户ID
     * @return 消息统计信息
     */
    @Select("SELECT " +
            "COUNT(*) as total_count, " +
            "SUM(CASE WHEN is_read = 0 THEN 1 ELSE 0 END) as unread_count, " +
            "SUM(CASE WHEN is_read = 1 THEN 1 ELSE 0 END) as read_count, " +
            "SUM(CASE WHEN priority = 4 THEN 1 ELSE 0 END) as urgent_count " +
            "FROM driver_message WHERE driver_id = #{driverId} AND DATE(create_time) = CURDATE() " +
            "AND tenant_id = #{tenantId} AND deleted = 0")
    @MapKey("total_count")
    java.util.Map<String, Object> selectTodayStatistics(@Param("driverId") Long driverId, @Param("tenantId") Long tenantId);
}